var express = require('express');
var router = express.Router();
var sqlite3 = require('sqlite3').verbose();
var path = require('path');
/* GET users listing. */
router.get('/', function (req, res, next) {
    var db = new sqlite3.Database(path.join(__dirname, "../db/LH.s3db"));
    var year = new Date().getFullYear();
    var lastyear = parseInt(year) - 1;
    if (req.query.status == 0) { //加载表的基本信息
        try {
            sqlStr1 = "select * from student join college on student.college_no=college.college_no join class on student.class=class.class_name join class_teacher on class.ct_id=class_teacher.ct_id where s_id=?";
            db.all(sqlStr1, [req.session.username], function (err, rows) {
                var re = {};
                re.data = rows[0];
                re.count = rows.length;
                res.status(200).json(re);
            });
        } catch (ex) {
            console.log(ex);
        }
    } else if (req.query.status == 1) { //查询是否已填写量化表
        try {
            sqlStr1 = "select * from lh_table where s_id=? and year=?";
            db.all(sqlStr1, [req.session.username, lastyear + '-' + year], function (err, rows) {
                if (rows.length == 0) {
                    res.send('0');
                } else {
                    res.send(rows[0].status);
                }
            });
        } catch (ex) {
            console.log(ex);
        }
    } else if (req.query.status == 2) { //加载初始量化表
        try {
            sqlStr1 = "select * from student join s_activity on student.s_id=s_activity.s_id join activity on activity.a_no=s_activity.a_no where student.s_id=? and year=? and score>0";
            db.all(sqlStr1, [req.session.username, lastyear + '-' + year], function (err, rows) {
                if (rows.length == 0) {
                    res.send('0');
                } else {
                    var re = {};
                    var data = [];
                    var n = 0;
                    for (var i = 0; i < rows.length; i++) {
                        var a_year = rows[i].time.split('/')[0];
                        var a_month = rows[i].time.split('/')[1];
                        if ((a_year == year && a_month < 9) || (a_year == lastyear && a_month >= 9)) {
                            data[n] = rows[i];
                            if (a_year == year && a_month < 3)
                                data[n].term = 1 //上学期
                            else
                                data[n].term = 2 //下学期
                            n++;
                        }
                    }
                    re.data = data;
                    re.count = n;
                    res.status(200).json(re);
                }
            });
        } catch (ex) {
            console.log(ex);
        }
    } else if (req.query.status == 3) {
        try {
            sqlStr1 = "select * from lh_compose join lh_table on lh_compose.t_no=lh_table.t_no join student on student.s_id=lh_table.s_id where student.s_id=? and year=?"
            db.all(sqlStr1, [req.session.username, lastyear + '-' + year], function (err, rows) {
                if (rows.length == 0) {
                    res.send('0');
                } else {
                    var re = {};
                    re.data = rows;
                    re.count = rows.length;
                    res.status(200).json(re);
                }
            });
        } catch (ex) {
            console.log(ex);
        }
    } //加载填写的量化表
    else if (req.query.status == 4) { //生成量化表
        var data = req.query.list;
        if (req.query.refill == 0) {
            try {
                sqlStr1 = "select * from lh_table";
                db.all(sqlStr1, function (err, rows) {
                    var t_no = rows.length + 1;
                    db.run("INSERT INTO lh_table VALUES(?,?,?,?)",
                        t_no, req.session.username, lastyear + '-' + year, '1',
                        function (err) {
                            if (err == null) {
                                sqlStr2 = "select * from lh_compose";
                                db.all(sqlStr2, function (err, rows1) {
                                    var item_no = rows1.length + 1;
                                    var sql = "";
                                    for (var i = 0; i < data.length; i++) {
                                        sql += "insert into lh_compose values('" + t_no + "','" + item_no + "','" + data[i].item_name + "','" + data[i].score + "','" + data[i].type + "','" + data[i].term + "','" + data[i].proof + "');";
                                        item_no++;
                                    }
                                    db.exec(sql, function (err) {
                                        if (err == null) {
                                            req.session.t_no = t_no;
                                            res.send("1");
                                        }

                                    });
                                });
                            } else {
                                res.send("0");
                                console.log(err);
                            }
                        });
                });
            } catch (ex) {
                console.log(ex);
            }
        } else if (req.query.refill == 1) {
            try {
                sqlStr1 = "select * from lh_table where s_id=? and year=?";
                db.all(sqlStr1, [req.session.username, lastyear + '-' + year], function (err, rows) {
                    if (rows.length != 0) {
                        var t_no = rows[0].t_no;
                        sqlStr2 = "update lh_table set status='1' where t_no=?";
                        db.run(sqlStr2, t_no, function (err) {
                            if (err == null) {
                                sqlStr3 = "delete from lh_compose where t_no=?";
                                db.run(sqlStr3, t_no, function (err) {
                                    if (err == null) {
                                        sqlStr4 = "select * from lh_compose";
                                        db.all(sqlStr4, function (err, rows1) {
                                            var item_no = rows1.length + 1;
                                            var sql = "";
                                            for (var i = 0; i < data.length; i++) {
                                                sql += "insert into lh_compose values('" + t_no + "','" + item_no + "','" + data[i].item_name + "','" + data[i].score + "','" + data[i].type + "','" + data[i].term + "','" + data[i].proof + "');";
                                                item_no++;
                                            }
                                            db.exec(sql, function (err) {
                                                if (err == null) {
                                                    req.session.t_no = t_no;
                                                    res.send("1");
                                                }
                                            });
                                        });
                                    } else {
                                        res.send("0");
                                        console.log(err);
                                    }
                                });
                            } else {
                                res.send("0");
                                console.log(err);
                            }
                        });
                    }
                })
            } catch (ex) {
                console.log(ex);
            }
        }
    } else if (req.query.status == 5) { //添加量化表日志
        try {
            sqlStr1 = "select * from lh_table where s_id=? and year=?";
            if (req.query.mode == 1)
                s_id = req.session.username;
            else if (req.query.mode == 2)
                s_id = req.query.sno;
            db.all(sqlStr1, [s_id, lastyear + '-' + year], function (err, rows) {
                if (rows.length != 0) {
                    var t_no = rows[0].t_no;
                    sqlStr2 = "select * from lh_table_log";
                    db.all(sqlStr2, function (err, rows) {
                        var log_no = rows.length + 1;
                        var now = new Date();
                        var year = now.getFullYear();
                        var month = now.getMonth() + 1;
                        var date = now.getDate();
                        var hour = now.getHours();
                        var minute = now.getMinutes();
                        var second = now.getSeconds();
                        var time = year + "-" + month + "-" + date + " " + hour + ":" + minute + ":" + second;
                        var content = req.query.content;
                        db.run("INSERT INTO lh_table_log VALUES (?,?,?,?)", t_no, time, content, log_no, function (err) {
                            if (err == null)
                                res.send("1");
                            else {
                                res.send("0");
                                console.log(err);
                            }
                        });
                    });
                } else {
                    res.send('0');
                    console.log(err);
                }
            });
        } catch (ex) {
            console.log(ex);
        }
    } else if (req.query.status == 6) { //将量化表提交给班主任
        try {
            sqlStr1 = "select * from lh_table where s_id=? and year=?";
            db.all(sqlStr1, [req.session.username, lastyear + '-' + year], function (err, rows) {
                if (rows.length != 0) {
                    var t_no = rows[0].t_no;
                    sqlStr2 = "update lh_table set status='2' where t_no=?";
                    db.run(sqlStr2, t_no, function (err) {
                        if (err == null) {
                            sqlStr3 = "select * from student join class on student.class=class.class_name join class_teacher on class.ct_id=class_teacher.ct_id where s_id=?";
                            db.all(sqlStr3, [req.session.username], function (err, rows) {
                                if (rows.length != 0) {
                                    var re = {}
                                    re.data = rows[0];
                                    re.count = rows.length;
                                    res.status(200).json(re);
                                } else {
                                    res.send('0');
                                    console.log(err);
                                }
                            });
                        } else {
                            res.send('0');
                            console.log(err);
                        }
                    });
                }
            });
        } catch (ex) {
            console.log(ex);
        }
    } else if (req.query.status == 7) {
        if (req.query.mode == "class_teacher") { //班主任浏览量化表
            try {
                sqlStr1 = "select * from lh_compose join lh_table on lh_compose.t_no=lh_table.t_no join student on student.s_id=lh_table.s_id join class on student.class = class.class_name  join college on student.college_no =college.college_no where student.s_id = (select min(s_id) from lh_table where status='2' group by year ) and ct_id=? and year=?";
                db.all(sqlStr1, [req.session.username, lastyear + '-' + year], function (err, rows) {
                    if (rows.length == 0) {
                        res.send('0');
                    } else {
                        var re = {};
                        re.data = rows;
                        re.count = rows.length;
                        res.status(200).json(re);
                    }
                });
            } catch (ex) {
                console.log(ex);
            }
        } else if (req.query.mode == "commitee") {
            try {
                sqlStr1 = "select * from lh_compose join lh_table on lh_compose.t_no=lh_table.t_no join student on student.s_id=lh_table.s_id join class on student.class = class.class_name  join college on student.college_no =college.college_no join class_teacher on class.ct_id=class_teacher.ct_id join commitee on commitee.college_no=college.college_no where student.s_id = (select min(s_id) from lh_table where status='3' group by year ) and com_id=? and year=?";
                db.all(sqlStr1, [req.session.username, lastyear + '-' + year], function (err, rows) {
                    if (rows.length == 0) {
                        res.send('0');
                    } else {
                        var re = {};
                        re.data = rows;
                        re.count = rows.length;
                        res.status(200).json(re);
                    }
                });
            } catch (ex) {
                console.log(ex);
            }
        }
    } else if (req.query.status == 8) { //量化表翻页
        if (req.query.mode2 == "class_teacher") {
            try {
                if (req.query.mode == "next") //下一页
                    sqlStr1 = "select * from lh_compose join lh_table on lh_compose.t_no=lh_table.t_no join student on student.s_id=lh_table.s_id join class on student.class = class.class_name  join college on student.college_no =college.college_no where student.s_id = (select s_id from lh_table where s_id>? and status='2' order by s_id limit 1) and ct_id=? and year=?"
                else if (req.query.mode == "pre") //上一页
                    sqlStr1 = "select * from lh_compose join lh_table on lh_compose.t_no=lh_table.t_no join student on student.s_id=lh_table.s_id join class on student.class = class.class_name  join college on student.college_no =college.college_no where student.s_id = (select s_id from lh_table where s_id<? and status='2' order by s_id limit 1) and ct_id=? and year=?";
                db.all(sqlStr1, [req.query.sno, req.session.username, lastyear + '-' + year], function (err, rows) {
                    if (rows.length == 0) {
                        res.send('0');
                    } else {
                        var re = {};
                        re.data = rows;
                        re.count = rows.length;
                        res.status(200).json(re);
                    }
                });
            } catch (ex) {
                console.log(ex);
            }
        } else if (req.query.mode2 == "commitee") {
            try {
                if (req.query.mode == "next") //下一页
                    sqlStr1 = "select * from lh_compose join lh_table on lh_compose.t_no=lh_table.t_no join student on student.s_id=lh_table.s_id join class on student.class = class.class_name  join college on student.college_no =college.college_no join commitee on commitee.college_no=college.college_no where student.s_id = (select s_id from lh_table where s_id>? and status='3' order by s_id limit 1) and com_id=? and year=?"
                else if (req.query.mode == "pre") //上一页
                    sqlStr1 = "select * from lh_compose join lh_table on lh_compose.t_no=lh_table.t_no join student on student.s_id=lh_table.s_id join class on student.class = class.class_name  join college on student.college_no =college.college_no join commitee on commitee.college_no=college.college_no where student.s_id = (select s_id from lh_table where s_id<? and status='3' order by s_id limit 1) and com_id=? and year=?"
                db.all(sqlStr1, [req.query.sno, req.session.username, lastyear + '-' + year], function (err, rows) {
                    if (rows.length == 0) {
                        res.send('0');
                    } else {
                        var re = {};
                        re.data = rows;
                        re.count = rows.length;
                        res.status(200).json(re);
                    }
                });
            } catch (ex) {
                console.log(ex);
            }
        }

    } else if (req.query.status == 9) { //搜索量化表
        try {
            if (req.query.mode == 1) { //按照学号搜索
                if (req.query.mode2 == "class_teacher")
                    sqlStr1 = "select * from lh_compose join lh_table on lh_compose.t_no=lh_table.t_no join student on student.s_id=lh_table.s_id join class on student.class = class.class_name  join college on student.college_no =college.college_no where student.s_id = ? and ct_id=? and year=? and status='2'";
                else if (req.query.mode2 == "commitee")
                    sqlStr1 = "select * from lh_compose join lh_table on lh_compose.t_no=lh_table.t_no join student on student.s_id=lh_table.s_id join class on student.class = class.class_name  join college on student.college_no =college.college_no join commitee on commitee.college_no=college.college_no where student.s_id = ? and com_id=? and year=? and status='3'";
                db.all(sqlStr1, [req.query.sno, req.session.username, lastyear + '-' + year], function (err, rows) {
                    if (rows.length == 0) {
                        res.send('0');
                    } else {
                        var re = {};
                        re.data = rows;
                        re.count = rows.length;
                        res.status(200).json(re);
                    }
                });
            } else if (req.query.mode == 2) { //按照姓名搜索
                if (req.query.mode2 == "class_teacher")
                    sqlStr1 = "select * from lh_compose join lh_table on lh_compose.t_no=lh_table.t_no join student on student.s_id=lh_table.s_id join class on student.class = class.class_name  join college on student.college_no =college.college_no where student.s_name = ? and ct_id=? and year=? and status='2'";
                else if (req.query.mode2 == "commitee")
                    sqlStr1 = "select * from lh_compose join lh_table on lh_compose.t_no=lh_table.t_no join student on student.s_id=lh_table.s_id join class on student.class = class.class_name  join college on student.college_no =college.college_no join commitee on commitee.college_no=college.college_no where student.s_name = ? and com_id=? and year=? and status='3'";
                db.all(sqlStr1, [req.query.sname, req.session.username, lastyear + '-' + year], function (err, rows) {
                    if (rows.length == 0) {
                        res.send('0');
                    } else {
                        var re = {};
                        re.data = rows;
                        re.count = rows.length;
                        res.status(200).json(re);
                    }
                });
            } else if (req.query.mode == 3) { //按照学号和姓名搜索
                if (req.query.mode2 == "class_teacher")
                    sqlStr1 = "select * from lh_compose join lh_table on lh_compose.t_no=lh_table.t_no join student on student.s_id=lh_table.s_id join class on student.class = class.class_name  join college on student.college_no =college.college_no where student.s_id = ? and student.s_name = ? and ct_id=? and year=? and status='2'";
                else if (req.query.mode2 == "commitee")
                    sqlStr1 = "select * from lh_compose join lh_table on lh_compose.t_no=lh_table.t_no join student on student.s_id=lh_table.s_id join class on student.class = class.class_name  join college on student.college_no =college.college_no join commitee on commitee.college_no=college.college_no where student.s_id = ? and student.s_name = ? and com_id=? and year=? and status='3'";
                db.all(sqlStr1, [req.query.sno, req.query.sname, req.session.username, lastyear + '-' + year], function (err, rows) {
                    if (rows.length == 0) {
                        res.send('0');
                    } else {
                        var re = {};
                        re.data = rows;
                        re.count = rows.length;
                        res.status(200).json(re);
                    }
                });
            }
        } catch (ex) {
            console.log(ex);
        }
    } else if (req.query.status == 10) { //审核量化表
        try {
            sqlStr1 = "select * from lh_table where s_id=? and year=?";
            var lh_status = "";
            if (req.query.mode == 1) //班主任通过审核
                lh_status = '3';
            else if (req.query.mode == -1) //班主任不通过审核
                lh_status = '-1';
            else if (req.query.mode == 2) //辅导员通过审核
                lh_status = '4';
            else if (req.query.mode == -2) //辅导员不通过审核
                lh_status = '-2';
            db.all(sqlStr1, [req.query.sno, lastyear + '-' + year], function (err, rows) {
                if (rows.length != 0) {
                    var t_no = rows[0].t_no;
                    sqlStr2 = "update lh_table set status=? where t_no=?";
                    db.run(sqlStr2, lh_status, t_no, function (err) {
                        if (err == null) {
                            res.send('1');
                        } else {
                            res.send('0');
                            console.log(err);
                        }
                    });
                }
            });
        } catch (ex) {
            console.log(ex);
        }
    } else if (req.query.status == 11) { //查看量化表审核状态
        try {
            sqlStr1 = "select * from lh_table where s_id=? and year=? ";
            db.all(sqlStr1, [req.session.username, lastyear + '-' + year], function (err, rows) {
                if (rows.length != 0) {
                    var t_no = rows[0].t_no;
                    sqlStr2 = "select * from lh_table_log where t_no=? order by log_no";
                    db.all(sqlStr2, [t_no], function (err, rows) {
                        if (rows.length == 0) {
                            res.send('0');
                        } else {
                            var re = {};
                            for (var i = 0; i < rows.length - 1; i++)
                                for (var j = i; j < rows.length - 1 - i; j++) {
                                    if (parseInt(rows[j].log_no) > parseInt(rows[j + 1].log_no)) {
                                        var t = rows[j];
                                        rows[j] = rows[j + 1];
                                        rows[j + 1] = t;
                                    }
                                }
                            // console.log(rows);
                            re.data = rows;
                            re.count = rows.length;
                            res.status(200).json(re);
                        }
                    });
                } else {
                    res.send('0');
                    console.log(err);
                }
            });
        } catch (ex) {
            console.log(ex);
        }
    }
});

module.exports = router;